/** 
 * @Title: BrandMonthDao.java 
 * @Package com.zizo.autohome.sta.sales.brandmonth 
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月27日 下午1:51:53 
 */
package com.zizo.autohome.sta.sales.segmentmonth;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.util.ConnectionPool;

/**
 * @ClassName: BrandMonthDao
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月27日 下午1:51:53
 * 
 */
public class SegMonthDao implements Runnable {
	private static final Connection conn = ConnectionPool.getConnection("carvoice_biz", "localhost");
	private int segid;

	public void createtable() {
		String sql1 = "create table biz_segsales_month_temp like biz_segsales_month";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void droptable() {
		String sql = "DROP TABLE IF EXISTS biz_segsales_month;";
		String sql1 = "ALTER TABLE biz_segsales_month_temp RENAME biz_segsales_month;";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getData() {
		String sql = "SELECT sales_segid,YEAR(sales_time),MONTH(sales_time),sales_time,SUM(sales_cnt) FROM biz_sales WHERE sales_segid="
				+ segid + " GROUP BY sales_segid,sales_time ORDER BY sales_segid";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int segid = rs.getInt(1);
				int year = rs.getInt(2);
				int month = rs.getInt(3);
				int cnt = rs.getInt(5);
				String time = rs.getString(4);
				int temp = getRate(time);// 上一个月的销量
				float rate = 0;
				if (temp != 0) {
					rate = 100 * (cnt - temp) / (float) temp;
				}
				SegMonth brand = new SegMonth(segid, year, month, cnt, rate);
				if (rate == 0) {
					insertIs0(brand);
				} else {
					insertNot0(brand);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getRate(String btime) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT SUM(sales_cnt) FROM biz_sales WHERE sales_segid=" + segid + " AND sales_time=DATE_SUB('"
				+ btime + "', INTERVAL 1 month) GROUP BY sales_segid,sales_time";
		int res = 0;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public void insertNot0(SegMonth brand) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO carvoice_biz.biz_segsales_month_temp(segsales_segid,segsales_year,segsales_month,segsales_volume,segsales_mrate) VALUES (?,?,?,?,ROUND(?,2));";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, brand.getSegid());
			ps.setInt(2, brand.getYear());
			ps.setInt(3, brand.getMonth());
			ps.setInt(4, brand.getVolume());
			ps.setFloat(5, brand.getMonthrate());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insertIs0(SegMonth brand) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO carvoice_biz.biz_segsales_month_temp(segsales_segid,segsales_year,segsales_month,segsales_volume) VALUES (?,?,?,?);";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, brand.getSegid());
			ps.setInt(2, brand.getYear());
			ps.setInt(3, brand.getMonth());
			ps.setInt(4, brand.getVolume());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void run() {
		getData();
		System.out.println(segid);
	}

	public SegMonthDao(int segid) {
		super();
		this.segid = segid;
	}

	public static void main(String[] args) {
		new SegMonthDao(0).createtable();
		ExecutorService fix = Executors.newFixedThreadPool(15);
		for (int i = 1; i < 16; i++) {
			fix.execute(new SegMonthDao(i));
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				new SegMonthDao(0).droptable();
				break;
			}
			try {
				Thread.sleep(10000);
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}
	}
}
